﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace AdoNetStudy
{
    class Program
    {
        const string CONNECTION_STRING = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=E:\Coding\HappyStudy\AdoNetStudy\AdoNetStudy\Test.mdf;Integrated Security=True";
        static void Main(string[] args)
        {
            Test12();
        }

        private static void Test1()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                var cmd = new SqlCommand("insert into Users values (N'段光伟')", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("insert into Users values (N'段光伟')", con);
                cmd.ExecuteNonQuery();
            }
        }


        private static void Test2()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                var cmd = new SqlCommand("insert into Users values (N'段光伟')", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("错误的 SQL 语句", con);
                cmd.ExecuteNonQuery();
            }
        }

        private static void Test3()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                using (var ts = con.BeginTransaction())
                {
                    var cmd = new SqlCommand("insert into Users values (N'段光伟')", con, ts);
                    cmd.ExecuteNonQuery();

                    cmd = new SqlCommand("错误的 SQL 语句", con, ts);
                    cmd.ExecuteNonQuery();

                    ts.Commit();
                }
            }
        }

        private static void Test4()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                using (var ts = new TransactionScope())
                {
                    var cmd = new SqlCommand("insert into Users values (N'段光伟')", con);
                    cmd.ExecuteNonQuery();

                    cmd = new SqlCommand("错误的 SQL 语句", con);
                    cmd.ExecuteNonQuery();

                    ts.Complete();
                }
            }
        }

        private static void Test5()
        {
            using (var ts = new TransactionScope())
            {
                using (var con = new SqlConnection(CONNECTION_STRING))
                {
                    con.Open();
                    var cmd = new SqlCommand("insert into Users values (N'段光伟')", con);
                    cmd.ExecuteNonQuery();

                    cmd = new SqlCommand("错误的 SQL 语句", con);
                    cmd.ExecuteNonQuery();
                }

                ts.Complete();
            }
        }

        private static void Test6()
        {
            using (var tsOutter = new TransactionScope())
            {
                using (var con = new SqlConnection(CONNECTION_STRING))
                {
                    con.Open();
                    var cmd = new SqlCommand("insert into Users values (N'段光伟')", con);
                    cmd.ExecuteNonQuery();
                }

                using (var con = new SqlConnection(CONNECTION_STRING))
                {
                    con.Open();
                    var cmd = new SqlCommand("错误的 SQL 语句", con);
                    cmd.ExecuteNonQuery();
                }

                tsOutter.Complete();
            }
        }

        private static void Test7()
        {
            using (var tsOutter = new TransactionScope())
            {
                using (var con = new SqlConnection(CONNECTION_STRING))
                {
                    con.Open();
                    using (var tsInner = con.BeginTransaction())
                    {
                        var cmd = new SqlCommand("insert into Users values (N'段光伟')", con, tsInner);
                        cmd.ExecuteNonQuery();
                        tsInner.Commit();
                    }
                }

                using (var con = new SqlConnection(CONNECTION_STRING))
                {
                    con.Open();
                    var cmd = new SqlCommand("错误的 SQL 语句", con);
                    cmd.ExecuteNonQuery();
                }

                tsOutter.Complete();
            }
        }

        private static void Test8()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                var cmd = new SqlCommand("delete from Users", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("insert into Users values (N'段光伟1')", con);
                cmd.ExecuteNonQuery();
            }

            for (var i = 0; i < 5; i++)
            {
                Task.Factory.StartNew((state) =>
                {
                    using (var ts = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
                    {
                        using (var con = new SqlConnection(CONNECTION_STRING))
                        {
                            con.Open();

                            var index = (int)state + 1;
                            var padding = new String(' ', (index - 1) * 3);

                            Console.WriteLine(string.Format("{0}第{1}次：开始读取", padding, index));
                            var cmd = new SqlCommand("select top 1 * from Users", con);
                            cmd.ExecuteReader().Close();

                            Console.WriteLine(string.Format("{0}第{1}次：开始休眠", padding, index));
                            System.Threading.Thread.Sleep(1000 * ((int)state + 1));

                            Console.WriteLine(string.Format("{0}第{1}次：开始修改", padding, index));
                            cmd = new SqlCommand("update Users set Name = N'段光宇" + state + "'", con);
                            cmd.ExecuteNonQuery();
                            Console.WriteLine(string.Format("{0}第{1}次：完成修改", padding, index));
                        }

                        ts.Complete();
                    }
                }, i).ContinueWith((t) =>
                {
                    Console.WriteLine(t.Exception.InnerException.Message);
                }, TaskContinuationOptions.OnlyOnFaulted);
            }

            Console.ReadLine();
        }

        private static void Test9()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                var cmd = new SqlCommand("delete from Users", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("insert into Users values (N'段光伟1')", con);
                cmd.ExecuteNonQuery();
            }

            for (var i = 0; i < 5; i++)
            {
                Task.Factory.StartNew((state) =>
                {
                    using (var ts = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
                    {
                        using (var con = new SqlConnection(CONNECTION_STRING))
                        {
                            con.Open();

                            var index = (int)state + 1;
                            var padding = new String(' ', (index - 1) * 3);

                            Console.WriteLine(string.Format("{0}第{1}次：开始读取", padding, index));
                            var cmd = new SqlCommand("select top 1 * from Users(holdlock)", con);
                            cmd.ExecuteReader().Close();

                            Console.WriteLine(string.Format("{0}第{1}次：开始休眠", padding, index));
                            System.Threading.Thread.Sleep(1000 * ((int)state + 1));

                            Console.WriteLine(string.Format("{0}第{1}次：开始修改", padding, index));
                            cmd = new SqlCommand("update Users set Name = N'段光宇" + state + "'", con);
                            cmd.ExecuteNonQuery();
                            Console.WriteLine(string.Format("{0}第{1}次：完成修改", padding, index));
                        }

                        ts.Complete();
                    }
                }, i).ContinueWith((t) =>
                {
                    Console.WriteLine(t.Exception.InnerException.Message);
                }, TaskContinuationOptions.OnlyOnFaulted);
            }

            Console.ReadLine();
        }

        private static void Test10()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                var cmd = new SqlCommand("delete from Users", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("insert into Users values (N'段光伟1')", con);
                cmd.ExecuteNonQuery();
            }

            for (var i = 0; i < 5; i++)
            {
                Task.Factory.StartNew((state) =>
                {
                    using (var ts = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead }))
                    {
                        using (var con = new SqlConnection(CONNECTION_STRING))
                        {
                            con.Open();

                            var index = (int)state + 1;
                            var padding = new String(' ', (index - 1) * 3);

                            Console.WriteLine(string.Format("{0}第{1}次：开始读取", padding, index));
                            var cmd = new SqlCommand("select top 1 * from Users", con);
                            cmd.ExecuteReader().Close();

                            Console.WriteLine(string.Format("{0}第{1}次：开始休眠", padding, index));
                            //System.Threading.Thread.Sleep(1000 * ((int)state + 1));
                            System.Threading.Thread.Sleep(2000);

                            Console.WriteLine(string.Format("{0}第{1}次：开始修改", padding, index));
                            cmd = new SqlCommand("update Users set Name = N'段光宇" + state + "'", con);
                            cmd.ExecuteNonQuery();
                            Console.WriteLine(string.Format("{0}第{1}次：完成修改", padding, index));
                        }

                        ts.Complete();
                    }
                }, i).ContinueWith((t) =>
                {
                    Console.WriteLine(t.Exception.InnerException.Message);
                }, TaskContinuationOptions.OnlyOnFaulted);
            }

            Console.ReadLine();
        }

        private static void Test11()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                var cmd = new SqlCommand("delete from Users", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("insert into Users values (N'段光伟1')", con);
                cmd.ExecuteNonQuery();
            }

            for (var i = 0; i < 5; i++)
            {
                Task.Factory.StartNew((state) =>
                {
                    using (var ts = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
                    {
                        using (var con = new SqlConnection(CONNECTION_STRING))
                        {
                            con.Open();

                            var index = (int)state + 1;
                            var padding = new String(' ', (index - 1) * 3);

                            Console.WriteLine(string.Format("{0}第{1}次：开始读取", padding, index));
                            var cmd = new SqlCommand("select top 1 * from Users(updlock)", con);
                            cmd.ExecuteReader().Close();

                            Console.WriteLine(string.Format("{0}第{1}次：开始休眠", padding, index));
                            System.Threading.Thread.Sleep(1000 * ((int)state + 1));

                            Console.WriteLine(string.Format("{0}第{1}次：开始修改", padding, index));
                            cmd = new SqlCommand("update Users set Name = N'段光宇" + state + "'", con);
                            cmd.ExecuteNonQuery();
                            Console.WriteLine(string.Format("{0}第{1}次：完成修改", padding, index));
                        }

                        ts.Complete();
                    }
                }, i).ContinueWith((t) =>
                {
                    Console.WriteLine(t.Exception.InnerException.Message);
                }, TaskContinuationOptions.OnlyOnFaulted);
            }

            Console.ReadLine();
        }

        private static void Test12()
        {
            using (var con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                var cmd = new SqlCommand("delete from Users", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("insert into Users values (N'段光伟')", con);
                cmd.ExecuteNonQuery();

                cmd = new SqlCommand("insert into Users values (N'段光宇')", con);
                cmd.ExecuteNonQuery();
            }

            for (var i = 0; i < 2; i++)
            {
                Task.Factory.StartNew((state) =>
                {
                    using (var ts = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
                    {
                        using (var con = new SqlConnection(CONNECTION_STRING))
                        {
                            con.Open();

                            var index = (int)state + 1;
                            var padding = new String(' ', (index - 1) * 3);
                            var name = index == 1 ? "段光宇" : "段光伟";

                            Console.WriteLine(string.Format("{0}第{1}次：开始修改", padding, index));
                            var cmd = new SqlCommand("update Users set Name = N'" + name + index + "' where Name = N'" + name + "'", con);
                            cmd.ExecuteNonQuery();
                            Console.WriteLine(string.Format("{0}第{1}次：完成修改", padding, index));

                            System.Threading.Thread.Sleep(10000);

                            Console.WriteLine(string.Format("{0}第{1}次：开始读取", padding, index));
                            cmd = new SqlCommand("select * from Users", con);
                            cmd.ExecuteReader().Close();
                            Console.WriteLine(string.Format("{0}第{1}次：完成读取", padding, index));
                        }

                        ts.Complete();
                    }
                }, i).ContinueWith((t) =>
                {
                    Console.WriteLine(t.Exception.InnerException.Message);
                }, TaskContinuationOptions.OnlyOnFaulted);
            }

            Console.ReadLine();
        }

        public static void Test13()
        {
            var tasks = new Task[10];
            for (var i = 1; i <= 10; i++)
            {
                tasks[i - 1] = Task.Factory.StartNew((state) =>
                {
                    int localSum, result;
                    do
                    {
                        using (var con = new SqlConnection(CONNECTION_STRING))
                        {
                            con.Open();
                            var cmd = new SqlCommand("select sum from Tests where Id = 1", con);
                            var reader = cmd.ExecuteReader();
                            reader.Read();
                            localSum = reader.GetInt32(0);

                            System.Threading.Thread.Sleep(10);
                            var value = (int)state;
                            var newValue = localSum + value;

                            cmd = new SqlCommand("update Tests set sum = " + newValue + " where sum = " + localSum + "", con);
                            result = cmd.ExecuteNonQuery();
                        }
                    } while (result == 0);
                }, i);
            }

            Task.WaitAll(tasks);
        }
    }
}
